﻿using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Services.Interface;
using Microsoft.EntityFrameworkCore;
using RndMaterialDisposalService.Repository;
using System.Linq;
using RndMaterialDisposalService.Attrbutes;
using Masuit.Tools;
using AngleSharp.Browser.Dom;
using Microsoft.Data.SqlClient;
using System.Data;
using System;

namespace RndMaterialDisposalService.Services
{
    [NAutowired(Lifecycle.Scoped)]
    public class CapitalCompensateReportFormService: ICapitalCompensateReportFormService
    {
        private readonly AssetsManagementContext dbContext;
        private readonly UserBaseInforDataContext userBaseInforDataContext;
        public CapitalCompensateReportFormService(AssetsManagementContext _dbContext, UserBaseInforDataContext _userBaseInforDataContext) 
        {
            dbContext = _dbContext;
            userBaseInforDataContext = _userBaseInforDataContext;
        }

        /// <summary>
        /// 返回资产赔偿电子流报表主数据(分页)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<CapitalCompensateReportFormModel> GetCapitalCompensateReportFormPageList(CapitalCompensateReportConditionModel input) 
        {
            List<CapitalCompensateReportFormModel> list = new List<CapitalCompensateReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(input.ApplicationNumber))
            {
                condition += $"AND V.ApplicationNumber LIKE '%{input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicantTime))
            {
                //condition += $"AND V.ApplicantTime >= '{input.ApplicantTime}' ";
                string[] timeArr = input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND V.ApplicantTime >= '{timeArr[0]} 00:00:00' AND V.ApplicantTime <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(input.Applicant))
            {
                condition += $"AND V.CompensateApplicantName = '{input.Applicant}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.CompanySegment)) 
            {
                condition += $"AND V.CompanySegment LIKE '%{input.CompanySegment}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.DepartmentName)) 
            {
                condition += $"AND (V.LargeSectors LIKE '%{input.DepartmentName}%' OR  V.SmallSector LIKE '%{input.DepartmentName}%') ";
            }
            if (!string.IsNullOrWhiteSpace(input.AssetNo)) 
            {
                condition += $"AND V.AssetNo LIKE '%'+@AssetNo+'%' ";
                SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
                sqlPara.Value = input.AssetNo;
                sqlParameters.Add(sqlPara);
            }
            if (!string.IsNullOrWhiteSpace(input.CurrentStatus)) 
            {
                condition += $"AND V.CurrentStatus = '{input.CurrentStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicantEmployeeNumber)) 
            {
                condition += $"AND V.ApplicantName LIKE '%{input.ApplicantEmployeeNumber}%' ";
            }

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                //获取当前用户资产赔偿电子流审批流程记录,去重后联查得到ProcInstId
                //string _sql = $"SELECT ProcInstId FROM AssetsManagement.dbo.CapitalCompensationBusinessData WHERE ProcessFolio IN (SELECT DISTINCT(ApplicationNumber) FROM VW_Common.dbo.WorkflowApproveData WHERE WorkflowName = '资产赔偿电子流' AND ApproverName = '{input.CurrentUser}')";
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.CapitalCompensationBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber)  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '资产赔偿电子流' AND ApproverName = '{input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql, new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = input.CurrentUser.Substring(0, input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {
                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='资产赔偿电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }
                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }
                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.CapitalCompensationBusinessSubtableData WHERE ProcInstId IN({ProcInstId_STR}))";
            }
            else 
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.CapitalCompensationBusinessSubtableData WHERE ProcInstId !='')";
            }

            int startIndex = ((input.pageIndex - 1) * input.pageSize) + 1;
            int endIndex = input.pageSize * input.pageIndex;

            string sql = @$"SELECT V.[Key],V.ApplicationNumber,V.ProcInstId,V.ApplicantEmployeeNumber,V.ApplicantName,V.AssetNo,V.Applicant,V.CompensateApplicantName,V.ApplicantTime,V.CompanySegment,V.LargeSectors,V.SmallSector,V.CurrentStatus,V.DepartmentCode,V.ShareName,V.ReasonsForAssetCompensation,V.CompensationAmount,V.StartDate FROM (SELECT ROW_NUMBER()  OVER(ORDER BY V.ApplicantTime DESC) AS RowID,V.* FROM (
                            SELECT capitalSub.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,TRIM(workflow.CurrentHandler) AS ApplicantName,capitalSub.AssetNo,capital.Applicant,TRIM(capital.ApplicantName) AS CompensateApplicantName,capital.ApplicantTime,capital.CompanySegment,capital.LargeSectors,capital.SmallSector,workflow.CurrentStatus,capital.DepartmentCode,capital.ShareName,capital.ReasonsForAssetCompensation,capitalSub.CompensationAmount,capitalSub.StartDate 
                            FROM {SqlInsertClip} capitalSub 
                            LEFT JOIN AssetsManagement.dbo.CapitalCompensationBusinessData capital ON capitalSub.ProcInstId = capital.ProcInstId  
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON capitalSub.ProcInstId = workflow.ProcInstId) V WHERE {condition}) V 
                            WHERE V.RowID BETWEEN {startIndex} AND {endIndex}";

            list = SqlQuery<CapitalCompensateReportFormModel>(sql, sqlParameters);

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }

            ////去掉当前审批人前缀
            //list.ForEach((x) => {
            //    if (!string.IsNullOrWhiteSpace(x.ApplicantName))
            //    {
            //        x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
            //    }
            //});
            return list;
        }

        /// <summary>
        /// 返回资产赔偿电子流报表主数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<CapitalCompensateReportFormModel> GetCapitalCompensateReportFormData(CapitalCompensateReportConditionModel input) 
        {
            List<CapitalCompensateReportFormModel> list = new List<CapitalCompensateReportFormModel>();
            List<SqlParameter> sqlParameters = new List<SqlParameter>();

            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(input.ApplicationNumber))
            {
                condition += $"AND workflow.ApplicationNumber LIKE '%{input.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.ApplicantTime))
            {
                //condition += $"AND capital.ApplicantTime >= '{input.ApplicantTime}' ";
                string[] timeArr = input.ApplicantTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND capital.ApplicantTime >= '{timeArr[0]} 00:00:00' AND capital.ApplicantTime <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(input.Applicant))
            {
                condition += $"AND capital.ApplicantName = '{input.Applicant}' ";
            }
            if (!string.IsNullOrWhiteSpace(input.CompanySegment))
            {
                condition += $"AND capital.CompanySegment LIKE '%{input.CompanySegment}%' ";
            }
            if (!string.IsNullOrWhiteSpace(input.DepartmentName))
            {
                condition += $"AND (capital.LargeSectors LIKE '%{input.DepartmentName}%' OR  capital.SmallSector LIKE '%{input.DepartmentName}%') ";
            }
            if (!string.IsNullOrWhiteSpace(input.AssetNo))
            {
                condition += $"AND capitalSub.AssetNo LIKE '%'+@AssetNo+'%' ";
                SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
                sqlPara.Value = input.AssetNo;
                sqlParameters.Add(sqlPara);
            }
            if (!string.IsNullOrWhiteSpace(input.CurrentStatus))
            {
                condition += $"AND workflow.CurrentStatus = '{input.CurrentStatus}' ";
            }

            if (!string.IsNullOrWhiteSpace(input.ApplicantEmployeeNumber)) 
            {
                //condition += $"AND workflow.ApplicantEmployeeNumber = '{input.ApplicantEmployeeNumber}' ";
                condition += $"AND workflow.ApplicantName LIKE '%{input.ApplicantEmployeeNumber}%' ";
   
            }

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                //获取当前用户资产赔偿电子流审批流程记录,去重后联查得到ProcInstId
                //string _sql = $"SELECT ProcInstId FROM AssetsManagement.dbo.CapitalCompensationBusinessData WHERE ProcessFolio IN (SELECT DISTINCT(ApplicationNumber) FROM VW_Common.dbo.WorkflowApproveData WHERE WorkflowName = '资产赔偿电子流' AND ApproverName = '{input.CurrentUser}')";
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.CapitalCompensationBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber AND WorkflowName = '资产赔偿电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '资产赔偿电子流' AND ApproverName = '{input.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = input.CurrentUser.Substring(0, input.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {

                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='资产赔偿电子流' AND ApplicantEmployeeNumber = '{employeeNumber}'";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }

                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.CapitalCompensationBusinessSubtableData WHERE ProcInstId IN({ProcInstId_STR}))";
            }
            else 
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.CapitalCompensationBusinessSubtableData WHERE ProcInstId !='')";
            }

            string sql = @$"SELECT capitalSub.SysId AS [Key],TRIM(workflow.ApplicationNumber)AS ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,TRIM(workflow.CurrentHandler) AS ApplicantName,capitalSub.AssetNo,capital.Applicant,capital.ApplicantName AS CompensateApplicantName,capital.ApplicantTime,capital.CompanySegment,capital.LargeSectors,capital.SmallSector,workflow.CurrentStatus,capital.DepartmentCode,capital.ShareName,capital.ReasonsForAssetCompensation,capitalSub.CompensationAmount,capitalSub.StartDate  
                            FROM {SqlInsertClip} capitalSub 
                            LEFT JOIN AssetsManagement.dbo.CapitalCompensationBusinessData capital ON capitalSub.ProcInstId = capital.ProcInstId 
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON capitalSub.ProcInstId = workflow.ProcInstId 
                            WHERE {condition}";


            list = SqlQuery<CapitalCompensateReportFormModel>(sql, sqlParameters);

            list.ForEach((x) => {
                x.ApplicationNumber = x.ApplicationNumber.Trim();
            });

            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(input.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.ApplicantTime = item.ApproveDate;
                    }
                });
            }
            ////去掉当前审批人前缀
            //list.ForEach((x) => {
            //    if (!string.IsNullOrWhiteSpace(x.ApplicantName))
            //    {
            //        x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
            //    }
            //});

            return list;
        }

        /// <summary>
        /// 新增资产赔偿电子流业务数据
        /// </summary>
        /// <param name="compensate"></param>
        /// <param name="compensateSubtable"></param>
        /// <returns></returns>
        public async Task<int> AddCapitalCompensateBusinessData(CapitalCompensateBusinessDataModel compensate, List<CapitalCompensateBusinessSubtableDataModel> compensateSubtable) 
        {
            var item = await dbContext.CapitalCompensateBusinessData.FirstOrDefaultAsync(a=>a.ProcInstId == compensate.ProcInstId);
            if (item != null)
            {
                dbContext.CapitalCompensateBusinessData.Remove(item);
            }

            var list = dbContext.CapitalCompensateBusinessSubtableData.Where(a=>a.ProcInstId == compensate.ProcInstId);
            if (list.Count() > 0) 
            {
                dbContext.CapitalCompensateBusinessSubtableData.RemoveRange(list);
            }

            dbContext.CapitalCompensateBusinessData.Add(compensate);
            dbContext.CapitalCompensateBusinessSubtableData.AddRange(compensateSubtable);
            return await dbContext.SaveChangesAsync();

        }

        /// <summary>
        /// 根据流程实例ID查询资产赔偿电子流信息
        /// </summary>
        /// <param name="ProcInstId"></param>
        /// <returns></returns>
        public async Task<CapitalCompensateBusinessDataModel> GetCapitalCompensateBusinessDataFind(string ProcInstId)
        {
            CapitalCompensateBusinessDataModel capitalCompensateBusinessData = null;
            if (!string.IsNullOrWhiteSpace(ProcInstId))
            {
                capitalCompensateBusinessData = await dbContext.CapitalCompensateBusinessData.FirstOrDefaultAsync(a => a.ProcInstId == ProcInstId);
            }
            return capitalCompensateBusinessData;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0) 
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }
    }
}
